﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using Npgsql;
using System.Collections;
using System.Text;
using System.Globalization;

namespace TeamProject
{
    /// <summary>
    /// Summary description for WebService1
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    [System.Web.Script.Services.ScriptService]
    public class WebService1 : System.Web.Services.WebService
    {

        static string rd(string stIn)
        {
            string stFormD = stIn.Normalize(NormalizationForm.FormD);
            StringBuilder sb = new StringBuilder();

            for (int ich = 0; ich < stFormD.Length; ich++)
            {
                UnicodeCategory uc = CharUnicodeInfo.GetUnicodeCategory(stFormD[ich]);
                if (uc != UnicodeCategory.NonSpacingMark)
                {
                    sb.Append(stFormD[ich]);
                }
            }

            return (sb.ToString().Normalize(NormalizationForm.FormC));
        }

        [WebMethod]
        public string[] getSuggestions(string prefixText, int count)
        {
            System.Collections.ArrayList suggestions = new ArrayList();
            
            using (NpgsqlConnection conn = new NpgsqlConnection("database=postgres;server=localhost;port=5432;UID=bassy;password=H2laF3zG"))
            {
                conn.Open();

                 //concepty
                String sql = "SELECT type FROM category WHERE to_ascii(convert_to(lower(type), 'latin2'),'latin2') LIKE '" + rd((prefixText.ToLower())) + "%'";
                NpgsqlCommand cmd = new NpgsqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = sql;

                NpgsqlDataReader dr;
                int i = 0;

                dr = cmd.ExecuteReader();
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        if (i < count)
                        {
                            suggestions.Add(((string)dr[0]) + "   (koncept)");
                            //Console.WriteLine(rowss[i][0].ToString());
                        }

                        i++;
                    }
                }


                String sql2 = "SELECT city FROM geo_object WHERE to_ascii(convert_to(lower(city), 'latin2'),'latin2') LIKE '" + rd(prefixText.ToLower()) + "%' GROUP BY city";
                NpgsqlCommand cmd2 = new NpgsqlCommand();
                cmd2.Connection = conn;
                cmd2.CommandText = sql2;

                NpgsqlDataReader dr2;
                
                dr2 = cmd2.ExecuteReader();
                if (dr2.HasRows)
                {
                    while (dr2.Read())
                    {
                        if (i < count)
                        {
                            suggestions.Add(((string)dr2[0]) + "   (mesto)");
                            //Console.WriteLine(rowss[i][0].ToString());
                        }

                        i++;
                    }
                }

                String sql21 = "SELECT street FROM streets WHERE to_ascii(convert_to(lower(street), 'latin2'),'latin2') LIKE '" + rd(prefixText.ToLower()) + "%'";
                NpgsqlCommand cmd21 = new NpgsqlCommand();
                cmd21.Connection = conn;
                cmd21.CommandText = sql21;

                NpgsqlDataReader dr21;
           
                dr21 = cmd21.ExecuteReader();
                if (dr21.HasRows)
                {
                    while (dr21.Read())
                    {
                        if (i < count)
                        {
                            suggestions.Add(((string)dr21[0]) + "   (ulica)");
                            //Console.WriteLine(rowss[i][0].ToString());
                        }

                        i++;
                    }
                }

                String sql3 = "SELECT name FROM geo_object WHERE to_ascii(convert_to(lower(name), 'latin2'),'latin2') LIKE '" + rd(prefixText.ToLower()) + "%'";
                NpgsqlCommand cmd3 = new NpgsqlCommand();
                cmd3.Connection = conn;
                cmd3.CommandText = sql3;

                NpgsqlDataReader dr3;
                
                dr3 = cmd3.ExecuteReader();
                if (dr3.HasRows)
                {
                    while (dr3.Read())
                    {
                        if (i < count)
                        {
                            suggestions.Add(((string)dr3[0]) + "   (objekt)");
                            //Console.WriteLine(rowss[i][0].ToString());
                        }

                        i++;
                    }
                }
             
             }

            return (string[])suggestions.ToArray(typeof(string)); ;
        }
    }
}
